﻿/**************************************************
*
 * 数据访问层  
 * 基本信息的数据访问
 * 获取项目号、列表显示、返回实体
 * Author：JXL
 
 *************************************/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using IDAL;
using Model;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    public class Basic_InfoDAL : BaseDAL, IBasic_Info
    {
        protected override string tablename
        {
            get { return "Basic_Info"; }
        }

        protected override Dictionary<string, bool> pkname
        {
            get
            {
                var id = new Dictionary<string, bool>();
                id.Add("basic_id", true);
                return id;
            }
        }
        /// <summary>
        /// 获取项目号
        /// </summary>
        /// <returns></returns>
        public string getid()
        {
            string str = "select top 1 projectNumber from Basic_Info order by  Basic_id desc";
            var id = DBhelper.Scalar(str);
            if (id != null)
            {
                if (id.ToString().Substring(0, 4) == DateTime.Now.Year.ToString())
                {
                    id = (Convert.ToInt32(id) + 1).ToString();
                }
                else
                {
                    id = DateTime.Now.Year.ToString() + "0001";
                }
            }
            else
            {
                id = DateTime.Now.Year.ToString() + "0001";
            }
            return id.ToString();
        }
        /// <summary>
        /// 列表显示
        /// </summary>
        /// <param name="pageindex"></param>
        /// <param name="pagesize"></param>
        /// <param name="wherestr"></param>
        /// <returns></returns>
        public pagelist<Basic_infoss> getlist(int pageindex, int pagesize, string wherestr,int name)
        {
            string tablename = "Basic_Info a,Loan_Info b,Department c ";
            string strwhere = " a.projectNumber=b.projectNumber and a.the_Department=c.department_Id and a.Basic_CreatePeople="+name+"" + wherestr;
            string strid = "a.projectNumber,c.department_Name,b.loan_Name,b.loan_TEL,a.basic_Quota,a.basic_CreateTime,a.basic_State";
            string sort = "basic_id";
            using (SqlConnection sql = new SqlConnection(HouseCommon.Common.conn))
            {
                sql.Open();
                SqlCommand cmd = new SqlCommand("Common_PageList", sql);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter[] pa ={
                                     new SqlParameter("@tab",SqlDbType.NVarChar,100),
                                     new SqlParameter("@pageindex",SqlDbType.Int),
                                     new SqlParameter("@pagesize",SqlDbType.Int),
                                     new SqlParameter("@pagecount",SqlDbType.Int),
                                     new SqlParameter("@strFld",SqlDbType.NVarChar,500),
                                     new SqlParameter("@strWhere",SqlDbType.NVarChar,500),
                                     new SqlParameter("@Sort",SqlDbType.NVarChar,50)
                                 };
                pa[0].Value = tablename;
                pa[1].Value = pageindex;
                pa[2].Value = pagesize;
                pa[3].Direction = ParameterDirection.Output;
                pa[4].Value = strid;
                pa[5].Value = strwhere;
                pa[6].Value = sort;
                cmd.Parameters.AddRange(pa);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable ta = new DataTable();
                da.Fill(ta);
                List<Basic_infoss> list = DBhelper.DBHelper.DataTable2List<Basic_infoss>(ta).ToList();

                pagelist<Basic_infoss> liss = new pagelist<Basic_infoss>();
                liss.getlist = list;
                var count=(int)pa[3].Value;
                
                if(count%pagesize==0)
                {
                    liss.pagecount = count/pagesize;
                }
                else
                {
                    liss.pagecount = (count / pagesize) + 1;
                }
                liss.count = count;
                return liss;
            }

        }
        /// <summary>
        /// 返回实体
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Basic_infoss getmodel(int id)
        {
            string str = "select a.ProjectNumber,d.loan_Name,d.loan_TEL,a.loan_Contacts,a.loan_ContactsTEL,a.surplus_Years,a.basic_Quota,a.basic_Total,a.house_Located,a.house_Purpose,a.house_Time,a.yers_Source,a.presentation_Type,a.assessment,b.department_Name,c.users_Name,e.users_Name,a.issue_Time,a.basic_CreateTime from Basic_Info a,Department b,Users c,Loan_Info d, Users e where a.ProjectNumber=d.ProjectNumber and a.The_Department=b.Department_Id and a.survey=c.Users_Id  and a.evaluation=e.Users_Id and a.ProjectNumber=" + id + "";
            using (SqlConnection sql = new SqlConnection(HouseCommon.Common.conn))
            {
                sql.Open();
                SqlCommand cmd = new SqlCommand(str, sql);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable ta = new DataTable();
                da.Fill(ta);
                return DBhelper.DBHelper.DataTable2List<Basic_infoss>(ta).FirstOrDefault();
            }
        }
        /// <summary>
        /// 修改状态
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int putstate(int id)
        {
            string str = "update Basic_Info set basic_State=1,survey_StartTime='"+DateTime.Now+"' where ProjectNumber=" + id + "";
            return DBhelper.NonQuery(str);
        }
        /// <summary>
        /// 重写的delete方法
        /// </summary>
        /// <param name="w"></param>
        /// <returns></returns>
        public override int delete(string w)
        {
            var sqlstr = "delete from " + tablename + " where projectNumber in (" + w + ");delete from Loan_info where projectnumber in ("+w+")";
            return DBhelper.NonQuery(sqlstr);
        }

    }
}
